package com.offcn.question.service.impl;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.offcn.common.utils.PageUtils;
import com.offcn.common.utils.Query;

import com.offcn.question.dao.QmsQuestionDao;
import com.offcn.question.entity.QmsQuestionEntity;
import com.offcn.question.service.QmsQuestionService;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;


@Service("qmsQuestionService")
public class QmsQuestionServiceImpl extends ServiceImpl<QmsQuestionDao, QmsQuestionEntity> implements QmsQuestionService {

    @Override
    public PageUtils queryPage(Map<String, Object> params) {
        //1、获取查询关键字
        String key= (String) params.get("key");
        //2、创建查询条件对象
        QueryWrapper<QmsQuestionEntity> queryWrapper = new QueryWrapper<>();
        //3、设置查询条件
        if(!StringUtils.isEmpty(key)){
            queryWrapper.eq("id",key).or().like("title",key);
        }
        IPage<QmsQuestionEntity> page = this.page(
                new Query<QmsQuestionEntity>().getPage(params),
                new QueryWrapper<QmsQuestionEntity>()
        );

        return new PageUtils(page);
    }

    //导入ecxel
    @Override
    public Map importExcel(MultipartFile file) {
        Map resultMap = new HashMap();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String fileName = file.getOriginalFilename();
        if (fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$") ){
            resultMap.put("result",false);
            resultMap.put("msg","文件格式不正确，导入失败");
            resultMap.put("num",0); //导入了多少条数据
        }
        List<QmsQuestionEntity> list = new ArrayList<>();
        try {
            InputStream inputStream = file.getInputStream();
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            XSSFSheet sheet = workbook.getSheetAt(0);   //获取第一张表
            //获取多少行
            int rows = sheet.getPhysicalNumberOfRows();
            //来个空值引用
            QmsQuestionEntity questionEntity=null;
            for (int i=1;i<rows;i++){   //从第二行开始，因为第一行是字段标题
                questionEntity= new QmsQuestionEntity();
                XSSFRow row = sheet.getRow(i);
                questionEntity.setTitle(row.getCell(0).getStringCellValue());
                questionEntity.setAnswer(row.getCell(1).getStringCellValue());
                questionEntity.setLevel((int)row.getCell(2).getNumericCellValue());
                questionEntity.setType((long)row.getCell(3).getNumericCellValue());
                questionEntity.setSubTitle(row.getCell(4).getStringCellValue());
                questionEntity.setDisplayOrder((int)row.getCell(5).getNumericCellValue());
                questionEntity.setEnable((int)row.getCell(6).getNumericCellValue());
                list.add(questionEntity);
                //调用接口保存到数据库
                this.save(questionEntity);
            }
            resultMap.put("result",true);
            resultMap.put("msg","导入成功");
            resultMap.put("num",list.size());
        } catch (IOException e) {
            e.printStackTrace();
        }
        return resultMap;
    }

    //导出excel
    @Override
    public Workbook exportExcel() {
        //创建一个新的Excel
        XSSFWorkbook workbook = new XSSFWorkbook();
        //创建一个工作簿
        XSSFSheet sheet = workbook.createSheet("题库");
        XSSFRow row = sheet.createRow(0);
        row.createCell(0).setCellValue("题目标题");
        row.createCell(1).setCellValue("题目答案");
        row.createCell(2).setCellValue("难度等级");
        row.createCell(3).setCellValue("排序");
        row.createCell(4).setCellValue("副标题");
        row.createCell(5).setCellValue("题目类型");
        row.createCell(6).setCellValue("是否显示");
        List<QmsQuestionEntity> list = this.list();
        for (int i=1;i<list.size();i++){
            XSSFRow row1 = sheet.createRow(i);
            row1.createCell(0).setCellValue(list.get(i).getTitle());
            row1.createCell(1).setCellValue(list.get(i).getAnswer());
            row1.createCell(2).setCellValue(list.get(i).getLevel());
            row1.createCell(3).setCellValue(list.get(i).getDisplayOrder());
            row1.createCell(4).setCellValue(list.get(i).getSubTitle());
            row1.createCell(5).setCellValue(list.get(i).getType());
            row1.createCell(6).setCellValue(list.get(i).getEnable());
        }
        return workbook;
    }

    @Resource
    private QmsQuestionDao questionDao;
    /**
     * 统计分类
     * Map<题目类型TYPE,个数num>
     */
    @Override
    public List<Map<String, Object>> countType() {
        QueryWrapper<QmsQuestionEntity> wrapper = new QueryWrapper<QmsQuestionEntity>()
                .select("TYPE,count(TYPE) as num")
                .groupBy("type");
        List<Map<String, Object>> mapList = questionDao.selectMaps(wrapper);
        return mapList;
    }

}